5 分钟介绍DuckDB：SQLite for Analytics

之前曾多次介绍到DuckDB，

今天再加一篇还不错的快速入门文章，

编译自: The 5 Minute Introduction to DuckDB: The SQLite for Analytics^[1]

作者: Shekhar Gulati

在阅读数据库周刊^[2]时，我第一次知道了 DuckDB 。当我快速了解为什么需要这种数据库时，它立即引起了我的注意。大多数开发人员习惯于在其本地开发环境中使用基于嵌入式文件的关系数据库。可嵌入 RDBMS 中最受欢迎的选择是SQLite^[3]。开发人员使用可嵌入的数据库，因为不需要进行设置，并且可以在几分钟内快速上手。这样可以进行快速原型制作，并且开发人员可以快速迭代业务功能。

DuckDB^[4]在某种意义上也类似于 SQLite，它也被设计用作可嵌入的数据库。开发人员可以轻松地将其作为库包含在其代码中并开始使用。在本文的后面，我将介绍如何在 Python 中使用 DuckDB。

为什么选择 DuckDB？

SQLite 主要集中在事务（OLTP）工作负载上。它的执行引擎适用于以 B 树存储格式存储的行。另一方面，DuckDB 在分析（OLAP）工作负载方面相当于 SQLite。下面是我从 SIGMOD 2019 在 DuckDB 上发表的论文^[5]中获得的图像，清楚地显示了 DuckDB 试图填补的空白。

如上图所示，DuckDB 填充了最右上象限。它是 Teradata 等 OLAP 数据库的可嵌入等效项。

像 DuckDB 这样的数据库旨在解决数据科学界对更快的嵌入式分析数据存储的实际需求。

像 DB2 这样的典型 RDBMS 对数据科学家来说是糟糕的入门经验。主要问题是：

• 安装过程复杂
• 设置与维护复杂

DuckDB 功能

DuckDB 开发于 2018 年 7 月开始。DuckDB 的主要功能是：

• 安装简单
• 嵌入式：无服务器管理
• 单文件存储格式
• 快速分析处理
• R/Python 和 RDBMS 之间的快速传输
• 不依赖任何外部状态。例如，单独的配置文件，环境变量。
• 单文件存储格式
• 可组合的界面。程序化 Fluent SQL API
• 通过 MVCC 完全 ACID

OLTP 和 OLAP 之间的区别

在继续进行之前，重要的是要了解 OLTP 和 OLAP 工作负载之间的区别。

OLTP 代表在线交易处理系统。他们的主要重点是支持面向用户的面向事务的应用程序。OLTP 系统的一个典型示例是在线商店。

OLAP 代表在线分析处理系统。他们的主要重点是帮助企业做出更好的决策。OLAP 数据库通常用于分析来自多个数据库的数据。它们用于财务预测，报告等。

OLTP	OLAP
它用于管理日常交易。它们有助于无缝运行业务。	它用于对数据进行分析。它们有助于使用数据预测未来
查询运行时间短，通常少于秒	查询很复杂，响应时间从几秒钟到几分钟到几小时不等
OLTP 表已规范化	OLAP 表被非规范化
OLTP 的特点是大量的短时间在线交易（插入，更新，删除）	OLAP 的特点是只读事务量相对较低。
OLTP 数据库被实现为面向行	OLAP 数据库通常以面向列的方式实现

DuckDB 是一个列式 OLAP 数据库

DuckDB 是面向列的可嵌入 OLAP 数据库。

像 SQLite 这样的典型 OLTP 关系数据库是面向行的。在面向行的数据库中，数据在物理上被组织为连续的元组。它是一次元组处理模型。下图显示了通常如何将数据库表中的记录按行存储到磁盘块中。我从 AWS Redshift 文档中获取了此图^[6]。

另一方面，柱状数据库按字段组织，使与字段关联的所有数据彼此相邻。列式数据库的此属性对于 OLAP 工作负载很有用。大多数 OLAP 查询通常仅访问列的子集，但访问这些列的大量行。下图显示了如何使用列式存储将每一列的值顺序存储到磁盘块中。

DuckDB 的两个优化特性：

1. DuckDB 使用向量化数据处理，通过有效利用 CPU 缓存，帮助开发更快的分析引擎。向量化处理背后的主要思想是处理列(或柱状数据)，并将从多列到元组(或行)的值的物化延迟到查询计划的很晚的时候——在我们需要将 resultset 投影回用户的时候。
2. DuckDB 使用多版本并发控制来符合 ACID。

DuckDB 用例

SIGMOD 2019年 论文^[7]中引用了 DuckDB 的两个重要用例：

1. 交互式数据分析：许多组织在数据科学家都帮助下来理解数据，以便他们可以做出更好的业务决策。如今，数据科学家在本地环境中探索数据的最流行方式是编写 Python 或 R 代码使用 Pandas，dplyr 等库。DuckDB 为想要使用 SQL 进行本地开发工作的数据科学家提供了另一种选择。
2. 边缘计算：最近几年，随着边缘计算的兴起，这种用例正变得越来越流行。边缘计算是一种分布式计算范例，它使计算和数据存储更接近需要的位置，从而缩短了响应时间并节省了带宽。使用像 DuckDB 这样的可嵌入数据库，可以在边缘上分析数据，从而更快地获得更好的结果。

DuckDB 与 Python 结合使用

您需要做的第一件事是使用 pip 安装 duckdb 库，

pip install duckdb

先下载本例所用的虚拟销售数据^[8]。

然后编写代码将 CSV 中的数据加载到表中，

import duckdb
conn = duckdb.connect('sales.duckdb')
cursor = conn.cursor()

cursor.execute("""
CREATE TABLE sales(
   Transaction_date date
  ,Product          varchar
  ,Price            bigint
  ,Payment_Type     varchar
  ,Name             varchar
  ,City             varchar
  ,State            varchar
  ,Country          varchar
  ,Account_Created  varchar
  ,Last_Login       varchar
  ,Latitude         double
  ,Longitude        double
)
"""
)

cursor.execute("COPY sales FROM 'sales.csv' (HEADER)")

print(cursor.execute('select count(*) from sales').fetchall())
cursor.close()
conn.close()

在上面显示的代码片段中，我们连接到数据库并执行了一些查询。首先，我们创建一个销售表，然后将它们从 CSV 文件复制到表中，最后进行计数查询。

如果运行此脚本，将看到如下所示的输出。

(venv) ➜  duckdb python app.py
[[998]]

该sales.csv有 998 条记录。

我们也可以执行分组查询。

query = """select city, sum(price)
from sales
group by city"""
print(cursor.execute(query).fetchdf())

上面将返回一个数据帧，如下所示。

                             city  sum(price)
0                   Prince Albert        3600
1                       Kobenhavn        1200
2                      Belleville        1200
3                          Aardal        1200
4                        Den Haag       19500
..                            ...         ...
754              Fort Saint James        1200
755                      Mettlach        1200
756                        Bergen        2400
757  Billings                            1200
758  Norcross                            1200

[759 rows x 2 columns]

我们还可以执行聚合查询，如下所示,

SELECT MIN(cnt), AVG(cnt),MAX(cnt) FROM
    (select count(*) as cnt FROM sales
    GROUP BY EXTRACT(DOY FROM Transaction_date::DATE),
    EXTRACT(HOUR FROM Transaction_date)
    ) stats

结论

DuckDB 是 CWI 数据库体系结构组的一个有趣的数据库。它解决了数据分析开发人员面临的难题。我认为这是对数据分析师工具箱的有用补充。

参考资料

[1]

The 5 Minute Introduction to DuckDB: The SQLite for Analytics: https://shekhargulati.com/2019/12/15/the-5-minute-introduction-to-duckdb-the-sqlite-for-analytics/

[2]

数据库周刊: https://dbweekly.com/

[3]

SQLite: https://www.sqlite.org/index.html

[4]

DuckDB: https://www.duckdb.org/

[5]

论文: https://hannes.muehleisen.org/SIGMOD2019-demo-duckdb.pdf

[6]

此图: https://docs.aws.amazon.com/redshift/latest/dg/c_columnar_storage_disk_mem_mgmnt.html

[7]

论文: https://hannes.muehleisen.org/SIGMOD2019-demo-duckdb.pdf

[8]

虚拟销售数据: https://support.spatialkey.com/spatialkey-sample-csv-data/